#!/usr/bin/env python
# coding: utf-8
#
# Copyright 2007 Google Inc.
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#

# https://developers.google.com/appengine/docs/python/gettingstarted/usingdatastore?hl=ja
from google.appengine.ext.search import SearchableModel
from google.appengine.ext import db
#from google.appengine.api import users
from google.appengine.ext.webapp.util import run_wsgi_app
import webapp2
import cgi

# ユーザ認証関係
from google.appengine.ext.webapp.util import login_required
#https://developers.google.com/appengine/docs/python/users/overview?hl=ja
from google.appengine.api import users

import re
import time
import logging

from google.appengine.ext import search
from google.appengine.ext.db import *

# 『時間がかかる処理はバックグラウンドに回して、リクエストハンドラーはさっさとブラウザーに応答する』というのは、Webアプリケーションの作成では基本的なテクニックみたいです。
# http://www.syousei.jp/2012/11/26/27
from google.appengine.api import taskqueue

def str2int(str_in):
    if str_in is None:
        return 0
    if len(str_in) == 0:
        return 0
    return int(str_in)

def str2fl(str_in):
    if str_in is None:
        return 0.0
    if len(str_in) == 0:
        return 0.0
    return float(str_in)

# DB間のリンクについて:
# https://groups.google.com/forum/?fromgroups=#!topic/google-appengine/dCZ3HBhG4RM
class mvc_itemtbl(search.SearchableModel):
    date        = db.DateTimeProperty(auto_now_add=True)
    name        = db.StringProperty(u"name")
    name_old    = db.StringProperty()
    author      = db.UserProperty()

class mvc_headtbl(db.Model):
    date    = db.DateTimeProperty(auto_now_add=True)
    server  = db.IntegerProperty()
    area    = db.IntegerProperty()
    alter   = db.IntegerProperty()
    address = db.StringProperty()
    xc      = db.FloatProperty()
    zc      = db.FloatProperty()
    price   = db.IntegerProperty()
    item_id = db.StringProperty()
    item_ref = db.ReferenceProperty()

class MainPage(webapp2.RequestHandler):
    def get(self):
        self.response.write("""<html><body>
<style type="text/css">
<!--
  .tblbd{
    border-collapse:collapse;
    border:#ccc solid 1px;
  }
  .tblbd td {border:#ccc solid 1px;}
//-->
</style>
""")
        qserver = self.request.get('server')
        self.response.write("<!-- server:%s-->\n" % qserver)
        iServer = 0
        chkd1=""
        chkd2=""
        chkd3=""
        if (qserver == '1'):
            iServer = 1
            chkd1 = "checked"
        if (qserver == '2'):
            iServer = 2
            chkd2 = "checked"
        if (qserver == '3'):
            iServer = 3
            chkd3 = "checked"

        qprice = self.request.get('price')
        qitem = self.request.get('item')
        self.response.write("<!-- price:%s-->\n" % qprice)
        self.response.write("<!-- item:%s-->\n" % qitem)
        mdq = re.search('"', qitem)
        if mdq is not None:
            qitem = re.sub('"', '&quot', qitem)
        self.response.write("<!-- item:%s-->\n" % qitem)
        qf2=''
        qf3=''
        if len(qprice) > 1:
            qf2 = qprice.encode('utf_8')
        if len(qitem) > 1:
            qf3 = qitem.encode('utf_8')
        # Write the submission form and the footer of the page
        self.response.write("""
<form action="/" method="get">
  <table class="tblbd">
    <tr>
      <TD>サーバ :<BR>
        <input type="radio" name="server" value="0" />指定なし<BR>
        <input type="radio" name="server" value="1" %s />E<BR>
        <input type="radio" name="server" value="2" %s />D<BR>
        <input type="radio" name="server" value="3" %s />P
      </TD>
      <TD>
        価格上限 :   <BR>     <!-- (例: 1000, 400K, 1M) : -->        
        <input type="text" name="price" rows="1" value="%s" size="10" /></TD>
      <TD> アイテム名 :<BR> 
        <input type="text" name="item" value="%s" size="30" />
        <input type="submit" value="検索"></TD>
    </tr>
  </table>
</form>""" % (chkd1, chkd2, chkd3, qf2, qf3) )
        if len(qitem) == 0:
            self.response.write("検索条件(アイテム名)を入れてください。<BR>")
            return
        records = None
        # ダブルクオートで囲ってある場合は、一致チェックする。
        qitem_key = re.sub('&quot', '', qitem)
        rec_cnt = 0
        if mdq is not None:
            self.response.write("<!-- item_key:%s -->\n" % qitem_key)
            sql1 = u"SELECT * FROM mvc_itemtbl WHERE name = '%s' " % qitem_key
            records = db.GqlQuery(sql1)
            rec_cnt = records.count()
            self.response.write("<!-- item_key_found:%d -->\n" % rec_cnt)
        if rec_cnt == 0:
            # アイテムテーブルから指定アイテム名を検索する
            records = mvc_itemtbl.all().search(qitem_key).order('-date').fetch(200)
            # .order('-date')
            if records is None:
                self.response.write('一致なし\n')
                self.response.write("</body></html>\n")
                return

        self.response.write('<table class="tblbd">\n')
        self.response.write('<tr><td align=center>品名</td><td align=center>詳細</td><td align=center>&nbsp;</td></tr>\n')
        cnt = 0
        iPrice = str2int(qprice)
        for elm in records:
            cnt += 1
            name1 = elm.name
            name1 = name1.encode('utf_8')
            item_key = elm.key().name()
            self.response.write("<tr>\n")
            self.response.write("<td>%s</td>\n" % (name1) )
            self.response.write("<td><a href=/Detail?server=%d&price=%d&" % ( iServer, iPrice) )
            self.response.write("item_key=%s&>\n" % item_key )
            self.response.write("表&nbsp;示</a></td>\n")
            self.response.write('<TD><a href="/Modify?item_key=%s">' % item_key)
            self.response.write('修&nbsp;正</a></td>\n')
            self.response.write("</tr>\n")
        self.response.write('%d件のアイテムがヒットしました。<BR>\n' %(cnt) )
        self.response.write('</table>\n')
        self.response.write("</body></html>\n")

class ItemDetail(webapp2.RequestHandler):
    def get(self):
        form_server = self.request.get('server')
        form_price = self.request.get('price')
        item_key = self.request.get('item_key')

        iServer = str2int(form_server)
        iPrice = str2int(form_price)
        self.response.write("""<html><HEAD><TITLE>アイテム詳細</TITLE></HEAD><body>
<style type="text/css">
<!--
  .tblbd{
    border-collapse:collapse;
    border:#ccc solid 1px;
  }
  .tblbd td {border:#ccc solid 1px;}
//-->
</style>
""")
        # http://stackoverflow.com/questions/3869858/gql-query-with-numeric-id-in-datastore-viewer
        rec = mvc_itemtbl.get_by_key_name(item_key)
        if rec is None:
            self.response.write(u"エラー: %sに対するアイテム名が見つかりません。\n" % ( unicode(item_key) ) )
            self.response.write("</body></html>\n")
            return
        item_name = rec.name
        if len(item_name) == 0:
            self.response.write(u"エラー: %sに対するアイテム名が見つかりません。\n" % ( unicode(item_key) ) )
            self.response.write("</body></html>\n")
            return
        self.response.write("%s: " % (item_name) )

        sql1 = "SELECT * FROM mvc_headtbl WHERE item_id = '%s'" % item_key
        if iServer > 0:
            sql1 = sql1 + " AND server = %s" % form_server
        if iPrice > 0:
            sql1 = sql1 + " AND price < %s" % form_price
        sql1 = sql1 + " LIMIT 50"
        self.response.write("<!-- SQL: %s  -->\n" % (sql1) )
        headers = db.GqlQuery(sql1)
        self.response.write("%d 件 ヒットしました。<BR>\n" % (headers.count()) )
        if headers.count() > 0:
            # 後方参照する(× トライ中)
            # http://d.hatena.ne.jp/shobonengine/20110628/1309252346
            #headers = mvc_headtbl.all().get()

            self.response.write('<table class="tblbd">\n')
            self.response.write('<tr><td align=center>日付</td><td align=center>サーバ</td><td align=center>エリア</td><td align=center>最寄り</td><td align=center>番地</td><td align=center>X</td><td align=center>Z</td><td align=center>価格</td><td align=center>品名</td></tr>\n')
            for elm in headers:
                Price_2 = re.sub(r'(\d)(?=(\d{3})+(?!\d))', '\\1,', str(elm.price))
                # 数値からエリア名などに変換する
                server1 = elm.server
                area1 = elm.area
                alter1 = elm.alter
                server2 = self.int_to_server(server1)
                area2 = self.int_to_area(area1)
                alter2 = self.int_to_alter(area1, alter1)
                adr1 = elm.address
                self.response.write('<!-- server2: %s -->\n' % server2)
                self.response.write('<!-- area2: %s -->\n' % area2)
                self.response.write('<!-- alter2: %s (%d) -->\n' % (alter2, alter1) )

                self.response.write('<tr><td>%d/%02d/%02d</td><td>%s</td><td>%s</td><td align=center>%s</td><td><a href =%s>%s</a></td><td>%s</td><td>%s</td><td align=right>%s</td><td>%s</td></tr>\n' % (
                    elm.date.year, elm.date.month, elm.date.day,
                    server2,
                    area2,
                    alter2,
                    self.generate_locator_link(server1, area1, adr1), adr1,
                    elm.xc, elm.zc,
                    Price_2,
                    item_name ))
        self.response.write('</table>\n')
        self.response.write('<a href=/?server=%s' % form_server)
        self.response.write('&price=%s' % form_price)
        qf3 = re.sub('<', '&lt', item_name)
        qf3 = re.sub('>', '&gt', qf3)
        qf3 = qf3.encode('utf_8')
        self.response.write('&item="%s">戻る</a>\n' % qf3)
        self.response.write("</body></html>\n")
    
    def int_to_server(self, srv):
        if (srv < 0 or srv > 3):
            return "ERROR"
        resp = ["None", "EMERALD", "DIAMOND", "PEARL"]
        return resp[srv]

    def int_to_area(self, area):
        if (area < 0 or area > 6):
            return "ERROR"
        resp = [u"なし", u"ソレス", u"ユグ", u"ゲオ北東", u"ゲオ南", u"ゲオ西", u"シス"]
        return resp[area]

    def int_to_alter(self, area, alter):
        if (area < 0 or area > 6):
            return "ERROR"
        if (alter < 0 or alter > 15):
            return "ERROR"
        if (area == 6):
            #           0       1       2       3       4       5       6       7       8       9       10      11      12      13      14      15
            resp = [ u"メイン", "A-1",  "A-2",  "B-1",  "B-2",  "B-3",  "C-1",  "C-2",  "C-3",  "D-1",  "D-2",  "E-1",  "E-2",  "F-1",  "F-2",  "G-1" ]
            return resp[alter]
        if alter == 0:
            return u"メイン"
        return "%s" % alter    # そのまま。

    # MoE Locatorへのリンクを生成する(シスは未対応。)
    def generate_locator_link(self, server, area, address):
        # http://m.syntacticsugar.net/?p;yug;9;4
        dmylink = "http://m.syntacticsugar.net/"
        if server is None:
            return dmylink
        if area is None:
            return dmylink
        if address is None:
            return dmylink
        if (area < 1 or area > 5):
            return dmylink
        if len(address) == 0:
            return dmylink
        ary_server = ["dummy", "e", "d", "p"]
        short_server = ary_server[server]
        ary_area = ["dummy", "thoress", "yug", "geoz-ne", "geoz-w", "geoz-s", "syth"]
        short_area = ary_area[area]
        adrr0 = int(address.split("-")[0])
        adrr1 = int(address.split("-")[1])
        return "http://m.syntacticsugar.net/?%s;%s;%d;%d" % (short_server, short_area, adrr0, adrr1)


class Modify(webapp2.RequestHandler):
    @login_required
    def get(self):
        item_key = self.request.get('item_key')
        self.response.write("""<html><HEAD><TITLE>アイテム名修正</TITLE></HEAD><body>
<style type="text/css">
<!--
  .tblbd{
    border-collapse:collapse;
    border:#ccc solid 1px;
  }
  .tblbd td {border:#ccc solid 1px;}
//-->
</style>
""")
        rec = mvc_itemtbl.get_by_key_name(item_key)
        if rec is None:
            self.response.write(u"エラー: %sに対するアイテム名が見つかりません。\n" % ( unicode(item_key) ) )
            self.response.write("</body></html>\n")
            return

        qf1 = rec.name
        qf1 = qf1.encode('utf_8')
        qf3 = ''
        qf31 = rec.name_old
        if qf31 is not None:
            qf3 = qf31
            qf3 = qf3.encode('utf_8')
        else:
            qf3 = '(未設定)'
        if len(qf3) == 0:
            qf3 = '(なし)'
        item_key = item_key.encode('utf_8')
        self.response.write("""
<form action="/PostModify" method="post">
  <table class="tblbd">
    <tr>
      <TD>
      現在のアイテム名: %s
      </TD>
      <TD> 新しいアイテム名 : <input type="text" name="item" value="%s" size="30" />
      </TD>
        <input type="hidden" name="item_key" value="%s" size="30" />
      <TD><input type="submit" value="保存"></TD>
    </tr>
    <tr>
      <TD>
      (過去の修正: %s)
      </TD>
    </tr>
  </table>
</form>""" % (qf1, qf1, item_key, qf3) )
        self.response.write("</body></html>\n")

class PostModify(webapp2.RequestHandler):
    def post(self):
        item = self.request.get('item')
        item_key = self.request.get('item_key')
        self.response.write("<html><HEAD><TITLE>アイテム名修正 POST</TITLE></HEAD><body>")
        rec = mvc_itemtbl.get_by_key_name(item_key)
        if rec is None:
            self.response.write(u"エラー: %sに対するアイテム名が見つかりません。\n" % ( unicode(item_key) ) )
            self.response.write("</body></html>\n")
            return
        rec.name_old = rec.name
        rec.name = item
        # 編集者の設定
        # https://developers.google.com/appengine/docs/python/gettingstarted/usingdatastore?hl=ja
        rec.author = users.get_current_user()
        rec.put()
        self.response.write("</body></html>\n")
        self.redirect('/Modify?item_key=%s' % item_key)

# for debug
class PostItem(webapp2.RequestHandler):
    def get(self):
        self.response.write('<html><body>')

        # Write the submission form and the footer of the page
        self.response.write("""
  <form action="/PostDebug" method="post">
    <div><input name="server" rows="1" cols="10"></div>
    <div><input name="area" rows="1" cols="30"></div>
    <div><input name="alter" rows="1" cols="10"></div>
    <div><input name="address" rows="1" cols="10"></div>
    <div><input name="xc" rows="1" cols="10"></div>
    <div><input name="zc" rows="1" cols="10"></div>
    <div><input name="price" rows="1" cols="10"></div>
    <div><input name="item" rows="2" cols="60"></div>
    <div><input type="submit" value="Post item"></div>
  </form>
</body>
</html>""")

class PostDebug(webapp2.RequestHandler):
    def post(self):

        self.response.write('<HTML><HEAD><TITLE>デバッグ用データ登録</TITLE></HEAD><body>')
        server = self.request.get('server')
        area = self.request.get('area')
        alter = self.request.get('alter')
        address = self.request.get('address')
        xc = self.request.get('xc')
        zc = self.request.get('zc')

        ppr = self.request.get('price')
        iprice = str2int(ppr)

        item = self.request.get('item')
        tick = "tick_test_12345"

        items = mvc_itemtbl.get_or_insert(key_name=tick, name=item)
        if items is None:
            items = mvc_itemtbl(key_name=tick)
            items.name = item
            items.put()
            self.response.write('<B>PUT OK</B><BR>')
        self.response.write('items: %s<BR>' % (items ) )

        if items is None:
            self.response.write('Error: No items' )
            return
        obj = mvc_headtbl()
        obj.server = str2int(server)
        obj.area = str2int(area)
        obj.alter = str2int(alter)
        obj.address = address
        obj.xc = str2fl(xc)
        obj.zc = str2fl(zc)
        obj.price = iprice
        obj.item_id = tick
        obj.item_ref = items
        obj.put()

        self.response.write('MVC_Insert_to_DB_OK<BR>')
        self.response.write(' respo: %s<BR>' % (self.request))
        self.response.write(' server: %s<BR>' % (server))
        self.response.write(' area: %s<BR>' % (area))
        self.response.write(' alter: %s<BR>' % (alter))
        self.response.write(' address: %s<BR>' % (address))
        self.response.write(' xc: %s<BR>' % (xc))
        self.response.write(' zc: %s<BR>' % (zc))
        self.response.write(' price: %d<BR>' % (iprice))
        self.response.write(' item: %s(KEY is %s)<BR>' % (item, tick))

class PostAHK(webapp2.RequestHandler):
    def post(self):
        taskqueue.add(
            url='/PostAHK_Worker',
            params={'server': self.request.get('server'),
                'area': self.request.get('area'),
                'alter': self.request.get('alter'),
                'address': self.request.get('address'),
                'xc': self.request.get('xc'),
                'zc': self.request.get('zc'),
                'records': self.request.get('records'),
                'tick': self.request.get('tick'),
            }
        )
        self.response.write('MVC_Insert_to_DB_OK\n')
        self.response.write('Task was added.\n')
        return

class PostAHK_Worker(webapp2.RequestHandler):
    def post(self):
        tick = self.request.get('tick')
        server = self.request.get('server')
        area = self.request.get('area')
        alter = self.request.get('alter')
        address = self.request.get('address')
        xc = self.request.get('xc')
        zc = self.request.get('zc')
        records = self.request.get('records')

        self.response.write('<HTML><HEAD><TITLE>一括登録結果</TITLE></HEAD><body>')
        logging.info("%s %s %s %s %s %s %s %s" %(
            tick, server, area, alter, address, xc, zc, records))

        cnt = 0
        dblist = []
        key_map = []
        rect = records.split('\n')
        for rec in rect:
            cnt = cnt + 1
            if len(rec) < 3:
                continue
            price = rec.split('\t')[0]
            iprice = str2int(price)

            item = rec.split('\t')[1]
            if item is None:
                item = price
            if item is None:
                continue
            item = re.sub('[ ]+$', '', item)
            # アイテムをアイテムテーブルから検索
            item_id = ''
            ent_item = None
            sql1 = "SELECT * FROM mvc_itemtbl WHERE name = '%s' " % (item)
            items = db.GqlQuery(sql1)
            for elm in items:
                item_id = elm.key().name()
                ent_item = elm
                self.response.write('<I>The item already registered found. ID: %s</I><BR>' % ( item_id ) )
                break

            # アイテムが見つからなかった... テーブル追加。
            if ent_item is None:
                tick2 = "%s-%02d" % (tick, cnt)
                rec = mvc_itemtbl.get_or_insert(key_name=tick2, name=item)
                if rec is None:
                    # アイテム初回登録
                    rec = mvc_itemtbl(key_name=tick)
                    rec.name = item
                    rec.put()
                    #self.response.write('Put mvc_itemtbl %s done. Key is %s %d<BR>\n' % (item, key, item_id) )
                    self.response.write('<B>Put itemTBL %s done.</B><BR>\n' % item)
                # 検索結果のIDを得る
                item_id = rec.key().name()
                ent_item = rec

            if ent_item is None:
                self.response.write('item %s key not found and failed to add mvc_itemtbl!! Exiting.<BR>\n' % item)
                continue   # 更にエラーの場合、何もしない。。

            obj = mvc_headtbl()
            obj.server = str2int(server)
            obj.area = str2int(area)
            obj.alter = str2int(alter)
            obj.address = address
            obj.xc = str2fl(xc)
            obj.zc = str2fl(zc)
            obj.price = iprice
            obj.item_id = item_id
            obj.item_ref = ent_item
            dblist.append(obj)
            
            self.response.write('[%d] price:%s\titem:%s<BR>\n' % (cnt, price, item))
            self.response.write('Append to mvc_headtbl done.<BR>\n')
        db.put(dblist)
        self.response.write('Put done.<BR>\n')
        self.response.write('MVC_Insert_to_DB_OK')

class Wipe(webapp2.RequestHandler):
    @login_required
    def get(self):
        # Wipe処理！ Debug環境以外では使用しないこと
        #lst1 = mvc_headtbl.all().fetch(1000)
        #db.delete(lst1)
        #lst2 = mvc_itemtbl.all().fetch(1000)
        #db.delete(lst2)
        return

app = webapp2.WSGIApplication([
    ('/', MainPage),
    ('/PostDebug',  PostDebug),
    ('/PostAHK',    PostAHK),
    ('/PostAHK_Worker',    PostAHK_Worker),
    ('/PostItem',   PostItem),
    ('/Detail',     ItemDetail),
    ('/Modify',     Modify),
    ('/PostModify', PostModify),
    ('/Wipe',       Wipe),
], debug=True)
